package com.basic.modules.ykf.controller;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.fastjson.TypeReference;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.basic.common.exception.RRException;
import com.basic.common.utils.PageUtils;
import com.basic.common.utils.ParamsModel;
import com.basic.common.utils.R;
import com.basic.common.validator.ValidatorUtils;
import com.basic.modules.sys.service.SysUserService;
import com.basic.modules.ykf.entity.*;
import com.basic.modules.ykf.model.CustomerManagementExcel;
import com.basic.modules.ykf.model.ExcelReq;
import com.basic.modules.ykf.service.*;
import com.basic.modules.ykf.utils.ExcelCellWidthStyleStrategy;
import com.basic.modules.ykf.utils.ExcelStyleUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;


/**
 * 字典
 *
 * @author maoheng
 * @email 15738859597@163.com
 * @date 2024-03-20 09:22:08
 */
@Slf4j
@RestController
@RequestMapping("ykf/excel")
public class YkfExcelController {

    @Autowired
    private YkfCustomerService customerService;

    @Autowired
    private YkfCustomerConsultService consultService;

    @Autowired
    private YkfWorkOvertimeRecordService ykfWorkOvertimeRecordService;

    @Autowired
    private YkfAccountInputRecordService accountInputRecordService;

    @Autowired
    private YkfPunishRecordService ykfPunishRecordService;

    @Autowired
    private YkfAccountInputRecordService ykfAccountInputRecordService;

    @Autowired
    private SysUserService sysUserService;

    @Autowired
    private YkfCusSerScoreService ykfCusSerScoreService;

    @Autowired
    private YkfCustomerSubAccountService customerSubAccountService;

    @Autowired
    private YkfMsgRecordService ykfMsgRecordService;

    @Value("${spring.datasource.druid.password: 123}")
    private String password;

    /**
     * 下载
     */
    @PostMapping("/download")
    public void download(@RequestBody ExcelReq req, HttpServletResponse response) throws IOException, SQLException {
        switch (req.getUrl()) {

            //客户咨询记录导出
            case "CustomerConsultationRecord":
                consultService.customerConsultationRecord(req, response);
                break;
            //客户店铺导出
            case "CustomerManagement":
                customerService.customerManagement(req, response);
                break;
            //店铺流失登记导出
            case "StoreChurnRegistration":
                customerService.storeChurnRegistration(req, response);
                break;
            //历史客户信息
            case "CustomerHistoricalData":
                customerService.customerHistoricalData(req, response);
                break;
            //客户效益查询导出
            case "CustomerBenefitInquiry":
                customerService.customerBenefitInquiry(req, response);
                break;
            //客服列表导出
            case "CustomerServiceList":
                sysUserService.customerServiceList(req, response);
                break;
            //客服考试分数导出
            case "CustomerServiceExamScores":
                ykfCusSerScoreService.customerServiceExamScores(req, response);
                break;
            //子账号管理导出
            case "SubAccountManagement":
                customerSubAccountService.subAccountManagement(req, response);
                break;
            //子账号分配导出
            case "SubAccountDistribution":
                customerSubAccountService.subAccountManagement(req, response);
                break;
            //已停用子账号导出
            case "SubAccountStop":
                customerSubAccountService.subAccountStop(req, response);
                break;
            //客服接待一览表导出
            case "CustomerReception":
                customerSubAccountService.customerReception(req, response);
                break;
            //加班登记导出
            case "OvertimeRegistration":
                ykfWorkOvertimeRecordService.overtimeRegistration(req, response);
                break;
//            //数据录入导出
            case "DataQueryInput":
                accountInputRecordService.dataQueryInput(req, response);
                break;
            //店铺每日绩效
            case "DataQueryAnalysis1":
                accountInputRecordService.dataQueryAnalysis1(req, response);
                break;
            //店铺整体绩效
            case "DataQueryAnalysis2":
                accountInputRecordService.dataQueryAnalysis2(req, response);
                break;
            case "DataQueryAnalysis2Detail":
                accountInputRecordService.dataQueryAnalysis2Detail(req, response);
                break;
            //接待量预警
            case "DataQueryAnalysis3":
                accountInputRecordService.dataQueryAnalysis3(req, response);
                break;
            //客服接待汇总表
            case "DataQueryAnalysis4":
                accountInputRecordService.dataQueryAnalysis4(req, response);
                break;
            //客服接待汇总表
            case "DataQueryAnalysis4Detail":
                accountInputRecordService.dataQueryAnalysis4Detail(req, response);
                break;
            //客服考核汇总表
            case "DataQueryAnalysis5":
                accountInputRecordService.dataQueryAnalysis5(req, response);
                break;
            //客服考核汇总表
            case "DataQueryAnalysis5Detail":
                accountInputRecordService.dataQueryAnalysis5Detail(req, response);
                break;
            //处罚管理
            case "QualityInspectionPenalty":
                ykfPunishRecordService.qualityInspectionPenalty(req, response);
                break;
            //
            case "StoreAllocation":
                customerSubAccountService.storeAllocation(req, response);
                break;
            //我的日接待汇总表
            case "Personal1":
                ykfAccountInputRecordService.personal1(req, response);
                break;
            //我的店铺
            case "Personal2":
                ykfAccountInputRecordService.personal2(req, response);
                break;
            //我的服务数据
            case "Personal3":
                ykfAccountInputRecordService.personal3(req, response);
                break;
            //我的提成核对表
            case "Personal4":
                ykfAccountInputRecordService.personal4(req, response);
                break;
            //客服提成核对表
            case "CommissionVerification":
                ykfAccountInputRecordService.commissionVerification(req, response);
                break;
            //客服提成核对汇总表
            case "MonthlyCommission":
                ykfAccountInputRecordService.monthlyCommission(req, response);
                break;
            //员工信息
            case "EmployeeManagement":
                sysUserService.employeeManagement(req, response);
                break;
            //服务费统计
            case "ServiceCharge":
                ykfAccountInputRecordService.serviceCharge(req, response);
                break;
            //短信发送记录
            case "SmsLog":
                ykfMsgRecordService.smsLog(req, response);
                break;
            //短信发送记录
            case "CustomerHotelManagement":
                export(req, response);
                break;
            default:
                throw new RRException("暂不支持页面" + req.getUrl() + "导出下载");
        }
    }

    /**
     * 下载模板
     */
    @PostMapping("/downloadTemp")
    public void downloadTemp(@RequestBody ExcelReq req, HttpServletResponse response) {

    }

    /**
     * 上传
     */
    @PostMapping("/upload")
    public R upload(MultipartFile file, @RequestBody ExcelReq req) {
        return R.ok();
    }

    /**
     * 导出
     */
    public void export(@RequestBody ExcelReq req, HttpServletResponse response) throws SQLException, IOException {
        ParamsModel<FaHotelsDataEntity> params = new ParamsModel<>();
        params.setCondition(req.getCondition(new TypeReference<FaHotelsDataEntity>() {
        }));
        params.setSort(req.getSort());
        ParamsModel.PageParams pageParams = new ParamsModel.PageParams();
        pageParams.setIndex(1L);
        pageParams.setSize(9999L);
        params.setPage(pageParams);

        List<ExcelFaHotelsData> list = new ArrayList<>();
        if (ObjectUtil.isNotEmpty(params) && ObjectUtil.isNotEmpty(params.getCondition()) && ObjectUtil.isNotEmpty(params.getPage())) {
            String dbUrl = "jdbc:mysql://localhost:3306/hotels_com";
            String userName = "root";
//            String password = "123456";
            Connection conn = DriverManager.getConnection(dbUrl, userName, password);
            Statement stmt = conn.createStatement();
            Statement stmt1 = conn.createStatement();
            Long index = params.getPage().getIndex();
            Long size = params.getPage().getSize();

            StringBuffer whereSql = new StringBuffer(" WHERE 1 = 1 ");
            FaHotelsDataEntity condition = params.getCondition();
            if (ObjectUtil.isNotEmpty(condition.getKeyword())) {
                whereSql.append(" AND ( a.hotelsId = \"" + condition.getKeyword() + "\"  ")
                        .append(" or ")
                        .append(" a.title = \"" + condition.getKeyword() + "\"  ) ");
            }
            if (ObjectUtil.isNotEmpty(condition.getHotelsId())) {
                whereSql.append(" AND a.hotelsId = \"" + condition.getHotelsId() + "\"  ");
            }
            if (ObjectUtil.isNotEmpty(condition.getTitle())) {
                whereSql.append(" AND a.title = \"" + condition.getTitle() + "\"  ");
            }
            if (ObjectUtil.isNotEmpty(condition.getHotelsCityName())) {
                whereSql.append(" AND b.name = \"" + condition.getHotelsCityName() + "\"  ");
            }
            if (ObjectUtil.isNotEmpty(condition.getOpeningYear())) {
                whereSql.append(" AND DATE_FORMAT(FROM_UNIXTIME(a.openingtime), '%Y') = " + condition.getOpeningYear() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getDecorationYear())) {
                whereSql.append(" AND DATE_FORMAT(FROM_UNIXTIME(a.decorationtime), '%Y') = " + condition.getDecorationYear() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getScoreStart())) {
                whereSql.append(" AND a.score >= " + condition.getScoreStart().doubleValue() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getScoreEnd())) {
                whereSql.append(" AND a.score <= " + condition.getScoreEnd().doubleValue() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getCooperationLevel())) {
                whereSql.append(" AND a.cooperation_level = \"" + condition.getCooperationLevel() + "\"  ");
            }
            if (ObjectUtil.isNotEmpty(condition.getStatus())) {
                whereSql.append(" AND a.status =  \"" + condition.getStatus() + "\" ");
            }
            if (ObjectUtil.isNotEmpty(condition.getCreatetimeStart())) {
                whereSql.append(" AND a.createtime >=  " + condition.getCreatetimeStart().getTime() / 1000 + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getCreatetimeEnd())) {
                whereSql.append(" AND a.createtime <=  " + condition.getCreatetimeEnd().getTime() / 1000 + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getRoomsNumStart())) {
                whereSql.append(" AND a.rooms_num >= " + condition.getRoomsNumStart() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getRoomsNumEnd())) {
                whereSql.append(" AND a.rooms_num <= " + condition.getRoomsNumEnd() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getScoreNumStart())) {
                whereSql.append(" AND a.score_num >= " + condition.getScoreNumStart() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getScoreNumEnd())) {
                whereSql.append(" AND a.score_num <= " + condition.getScoreNumEnd() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getStarRated())) {
                whereSql.append(" AND a.star_rated = \"" + condition.getStarRated() + "\" ");
            }
            log.info("SELECT " +
                    "count(1) " +
                    "FROM " +
                    " fa_hotels_data a " +
                    "LEFT JOIN fa_hotels_city b ON a.hotels_city_id = b.id " +
                    whereSql.toString());
            ResultSet resultSet1 = stmt1.executeQuery("SELECT " +
                    "count(1) " +
                    "FROM " +
                    " fa_hotels_data a " +
                    "LEFT JOIN fa_hotels_city b ON a.hotels_city_id = b.id " +
                    whereSql.toString());
            String orderSql = "";
            if (ObjectUtil.isNotEmpty(params.getSort()) && StrUtil.isNotBlank(params.getSort().getOrder()) && StrUtil.isNotBlank(params.getSort().getSidx())) {
                orderSql = "ORDER BY " + params.getSort().getSidx() + " " + params.getSort().getOrder() + " ";
            } else {
                orderSql = "ORDER BY createtime desc ";
            }
            String sql = "SELECT " +
                    " a.id, " +
                    " a.hotels_city_id as \"hotelsCityId\", " +
                    " a.hotelsId, " +
                    " a.title, " +
                    " a.address, " +
                    " a.tel, " +
                    " FROM_UNIXTIME(a.openingtime) as \"openingtime\", " +
                    " FROM_UNIXTIME(a.decorationtime) as \"decorationtime\", " +
                    " a.rooms_num as \"roomsNum\", " +
                    " a.score, " +
                    " a.score_num as \"scoreNum\", " +
                    " a.room_name_json as \"roomNameJson\", " +
                    " a.star_rated as \"starRated\", " +
                    " a.cooperation_level as \"cooperationLevel\", " +
                    " a.status, " +
                    " FROM_UNIXTIME(a.createtime) as \"createtime\", " +
                    " FROM_UNIXTIME(a.updatetime) as \"updatetime\", " +
                    "  b.name as \"hotelsCityName\" " +
                    "FROM " +
                    " fa_hotels_data a " +
                    "LEFT JOIN fa_hotels_city b ON a.hotels_city_id = b.id " +
                    whereSql.toString() +
                    orderSql +
                    "LIMIT " + (index - 1) * size + "," + size;
            ResultSet resultSet = stmt.executeQuery(sql);
            while (resultSet.next()) {
                ExcelFaHotelsData data = new ExcelFaHotelsData();
                data.setHotelsId(resultSet.getLong("hotelsId"));
                data.setTitle(resultSet.getString("title"));
                data.setAddress(resultSet.getString("address"));
                data.setTel(resultSet.getString("tel"));
//                data.setOpeningtime((resultSet.getInt("openingtime") == 0) ? null : resultSet.getTimestamp("openingtime"));
//                data.setDecorationtime((resultSet.getInt("decorationtime") == 0) ? null : resultSet.getTimestamp("decorationtime"));
                data.setOpeningtime(resultSet.getTimestamp("openingtime"));
                data.setDecorationtime(resultSet.getTimestamp("decorationtime"));
                data.setRoomsNum(resultSet.getInt("roomsNum"));
                data.setScore(resultSet.getBigDecimal("score"));
                data.setScoreNum(resultSet.getInt("scoreNum"));
                data.setRoomNameJson(resultSet.getString("roomNameJson"));
                data.setStarRated(resultSet.getString("starRated"));
                data.setCooperationLevel(resultSet.getString("cooperationLevel"));
                data.setStatus(resultSet.getString("status"));
                data.setHotelsCityName(resultSet.getString("hotelsCityName"));
                list.add(data);
            }
            if (ObjectUtil.isNotEmpty(list)) {
                for (ExcelFaHotelsData data : list) {
                    if (ObjectUtil.isNotEmpty(data.getOpeningtime()) && StrUtil.equals(DateUtil.format(data.getOpeningtime(), "yyyy"), "1970")) {
                        data.setOpeningtime(null);
                    }
                    if (ObjectUtil.isNotEmpty(data.getDecorationtime()) && StrUtil.equals(DateUtil.format(data.getDecorationtime(), "yyyy"), "1970")) {
                        data.setDecorationtime(null);
                    }
                }
            }
            conn.close();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("酒店信息", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(ExcelStyleUtils.getHeadStyle(), ExcelStyleUtils.getContentStyle());
            ExcelCellWidthStyleStrategy widthStyleStrategy = new ExcelCellWidthStyleStrategy();
            EasyExcel.write(response.getOutputStream(), ExcelFaHotelsData.class)
                    .sheet("酒店")
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .registerWriteHandler(widthStyleStrategy)
                    .doWrite(list);
        }
    }

}
